
module sqlite3d.Extra;

import 
	sqlite3d.Base,
	sqlite3d.Data,
	sqlite3d.pub;

char[] insert_sql(Row, char[] _if)(){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( is(  typeof(Row.mask) == int ) );
	char[] sql = "INSERT INTO " ~ Row.table_def.name ~ " (";
	foreach( i; Row.masks){
		if( i >=0 ){
			if( (Row.table_def.cols[i].col_type & ColType.ID) !is ColType.ID){
				sql ~= Row.table_def.cols[i].name ~ ",";
			}
		}
	}
	sql	= sql[0..$-1] ~ ") VALUES (";
	foreach( i; Row.masks){
		if( i >=0 ){
			if( (Row.table_def.cols[i].col_type & ColType.ID) !is ColType.ID){
				sql ~=   "?,";
			}
		}
	}
	sql	= sql[0..$-1] ~ ") ";
	return sql ~ _if ;
}

char[] update_sql(Row, char[] _if )(){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( is(  typeof(Row.mask) == int ) );
	char[] sql = "UPDATE " ~ Row.table_def.name ~ " SET ";
	foreach( i; Row.masks){
		if( i >=0 ){
			if( (Row.table_def.cols[i].col_type & ColType.ID) !is ColType.ID){
				sql ~= Row.table_def.cols[i].name ~ "=?,";
			}
		}
	}
	sql	= sql[0..$-1] ~ " WHERE ";
	return sql ~ _if ;
}

char[] select_sql(Row, char[] _if )(){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( is(  typeof(Row.mask) == int ) );
	char[] sql = " ";
	int oid	= -1;
	foreach( i; Row.masks){
		if( i >= 0 ){
			if( (Row.table_def.cols[i].col_type & ColType.ROWID) is ColType.ROWID ){
				oid	= i;
			}else{
				sql ~= Row.table_def.cols[i].name ~ ",";
			}
		}
	}
	if( oid is -1 ){
		sql	= "SELECT ROWID," ~ sql[0..$-1] ;
	}else{
		sql	=  "SELECT " ~  Row.table_def.cols[oid].name ~ "," ~ sql[0..$-1] ;
	}
	sql	~= " FROM " ~ Row.table_def.name ~ " WHERE ";
	return sql ~ _if ;
}

char[] select_count_sql(Row, char[] _if)(){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( is(  typeof(Row.mask) == int ) );
	char[] sql 	= "SELECT ";
	int oid	= TableRowId(Row.table_def);
	if( oid >= 0 ){
		sql	~= " count(" ~ Row.table_def.cols[oid].name ~ ") ";
	}else{
		sql	~= " count(ROWID) ";
	}
	sql	~= " FROM " ~ Row.table_def.name ~ " WHERE ";
	char[] _if2	= ctfe2_remove_right(_if, " LIMIT ");
	_if2	= ctfe2_remove_right(_if2, " ORDER BY ");
	return sql ~ _if2;
}

char[] delete_sql(Row, char[] _if )(){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( is(  typeof(Row.mask) == int ) );
	char[] sql = "DELETE FROM " ~ Row.table_def.name ~ " WHERE " ~ _if ;
	return sql ;
}
char[] Tsql_count(char[] sql){
	char[] sql2	= ctfe2_remove_right(ctfe2_remove_right(sql, " LIMIT "), " ORDER BY ");
	char[] from	= " FROM ";
	int iform	= ctfe2_ifind(sql2, from);
	if( iform == sql2.length){
		return "";
	}
	char[] group	= " GROUP BY ";
	int ig			= ctfe2_ifind(sql2, group);
	if( ig < sql2.length){
		ig	+= group.length ;
		char[] co_tmp	= ctfe2_ltrim(sql2[ ig ..$]);
		ig	= ctfe2_find(co_tmp, ' ');
		char[] field	= co_tmp[0..ig] ;
		char[] sql3	= sql2[ iform ..$];
		auto i	= ctfe2_ifind(sql3, " GROUP BY ");
		return "SELECT count(distinct " ~ field ~ ") " ~ sql3[0..i];
	}
	return "SELECT count(ROWID) " ~ sql2[ iform ..$];
}

template StaticStatementCache(){
	void compile(Statement* stmt){
		stmt.compile(sql);
	}
	
	static Statement*	_stmt;
	void open(Connection* con){
		close;
		_stmt	= Statement.Create(con);
	}
	void close(){
		if( _stmt ){
			_stmt.close;
			_stmt	= null;
		}
	}
	void compile(){
		_stmt.compile(sql);
	}
}

template RowUpdate(Row, char[] _if, T...){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( Row.tupleof.length > 0 );
	static assert( T.length >= 0 );
	static assert( T.length == ctfe2_CountQuestionMark(_if) , "the number of  `?` in `"~ _if~"` is not equal to tuple.length" );
	static const char[] sql = cast(char[]) update_sql!(Row,_if);
	//pragma(msg, sql);
	static const int	oid	= TableRowId(Row.table_def);
	mixin StaticStatementCache!();
	
	void step(Statement* stmt, Row* p, T t){
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, t);
		}else{
			stmt.bindTuple(p.tupleof[1..$], t);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.reset();
	}
	
	void update(Statement* stmt, Row* p, T t){
		stmt.compile(sql);
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, t);
		}else{
			stmt.bindTuple(p.tupleof[1..$], t);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	void step(Row* p, T t){
		step(_stmt, p , t);
	}
	void update(Row* p, T t){
		update(_stmt, p , t);
	}
}

template RowSelect(Row, char[] _if, T...){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( Row.tupleof.length > 0 );
	static assert( T.length >= 0 );
	static assert( T.length == ctfe2_CountQuestionMark(_if) );
	static const char[] sql	= cast(char[]) select_sql!(Row, _if);
	static const char[] sql_count = cast(char[]) select_count_sql!(Row, _if);
	static const int delta		= ctfe2_CountQuestionMark(sql) - ctfe2_CountQuestionMark(sql_count);
	alias T[0..$-delta] Tcount;
	mixin StaticStatementCache!();
	
	void select(Statement* stmt, T t){
		stmt.compile(sql);
		stmt.bindTuple(t);
	}
	
	bool fetchOne(Statement* stmt, Row* p){
		assert(p !is null);
		return stmt.fetchTuple(p.tupleof);
	}
	
	int count(Statement* stmt, Tcount tc){
		stmt.compile(sql_count);
		stmt.bindTuple(tc);
		int count;
		stmt.fetchTuple(count);
		stmt.finalize();
		return count;
	}
	
	void select(T t){
		select(_stmt, t);
	}
	bool fetchOne(Row* p){
		assert(p !is null);
		return _stmt.fetchTuple(p.tupleof);
	}
}

template RowInsert(Row, char[] _if = "", T...){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( Row.tupleof.length > 0 );
	static assert( T.length >= 0 );
	static assert( T.length == ctfe2_CountQuestionMark(_if) );
	static const char[] sql = cast(char[]) insert_sql!(Row, _if);
	//pragma(msg, sql);
	static const int	oid	= TableRowId(Row.table_def);
	mixin StaticStatementCache!();
	
	void step(Statement* stmt, Row* p, T t){
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, t);
		}else{
			stmt.bindTuple(p.tupleof[1..$], t);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.reset();
	}
	
	void insert(Statement* stmt, Row* p, T t){
		stmt.compile(sql);
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, t);
		}else{
			stmt.bindTuple(p.tupleof[1..$], t);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	void step(Row* p, T t){
		step(_stmt, p , t);
	}
	void insert(Row* p, T t){
		insert(_stmt, p , t);
	}
}

template RowDelete(Row, char[] _if = "", T...){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( Row.tupleof.length > 0 );
	static assert( T.length >= 0 );
	static assert( T.length == ctfe2_CountQuestionMark(_if) );
	static const char[] sql = cast(char[]) delete_sql!(Row, _if);
	//pragma(msg, sql);
	mixin StaticStatementCache!();
	
	void step(Statement* stmt, T t){
		stmt.bindTuple(t);
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
	}

	void del(Statement* stmt, T t){
		stmt.compile(sql);
		step(stmt, t);
	}
	
	void step(Row* p, T t){
		step(_stmt, p , t);
	}
	void del(T t){
		del(_stmt, t);
	}
}



template DefaultOperator(Row){
	static assert( is(  typeof(Row.table_def) == TableDef ) );
	static assert( is(  typeof(Row.masks) == int[] ) );
	static assert( Row.tupleof.length > 0 );
	static const int	oid		= TableRowId(Row.table_def);
	static const char[] _insert	= cast(char[]) insert_sql!(Row, "");
	static if( oid >=0 ){
		static const char[] _update	= cast(char[]) update_sql!(Row,  Row.table_def.cols[oid].name ~ "=?" );
		static const char[] _delete	= cast(char[]) delete_sql!(Row,  Row.table_def.cols[oid].name ~ "=?" );
		static const char[] _select	= cast(char[]) select_sql!(Row,  Row.table_def.cols[oid].name ~ "=? LIMIT 1" );
		static if( Row.table_def.cols[oid].type is DataType.INT ){
			alias int	RowType;
		}else static if( Row.table_def.cols[oid].type is DataType.UINT ){
			alias uint	RowType;
		}else static if( Row.table_def.cols[oid].type is DataType.INT64 ){
			alias long	RowType;
		}else static if( Row.table_def.cols[oid].type is DataType.UINT64 ){
			alias ulong	RowType;
		}
	}else{
		static const char[] _update	= cast(char[]) update_sql!(Row, "ROWID=?");
		static const char[] _delete	= cast(char[]) delete_sql!(Row,  "ROWID=?" );
		static const char[] _select	= cast(char[]) select_sql!(Row,  "ROWID=? LIMIT 1" );
		alias int	RowType;
	}
	//pragma(msg, Row.stringof);
	//pragma(msg, _insert);
	//pragma(msg, _update);
	//pragma(msg, _delete);
	
	static void pre_insert(Statement* stmt){
		stmt.compile(_insert);
	}
	static void pre_update(Statement* stmt){
		stmt.compile(_update);
	}
	static void pre_delete(Statement* stmt){
		stmt.compile(_delete);
	}
	
	static void step_insert(Statement* stmt, Row* p){
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof);
		}else{
			stmt.bindTuple(p.tupleof[1..$]);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.reset();
	}
	
	static void step_update(Statement* stmt, Row* p){
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, p.tupleof[0]);
		}else{
			stmt.bindTuple(p.tupleof[1..$], p.tupleof[0]);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.reset();
	}
	
	static void step_delete(Statement* stmt, Row* p){
		stmt.bindTuple(p.tupleof[0]);
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.reset();
	}
	
	static void Delete(Statement* stmt,  Row* p){
		stmt.compile(_delete);
		stmt.bindTuple(p.tupleof[0]);
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	static void Delete(Statement* stmt, RowType id){
		stmt.compile(_delete);
		stmt.bindTuple(id);
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	static void Insert(Statement* stmt,  Row* p){
		stmt.compile(_insert);
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof);
		}else{
			stmt.bindTuple(p.tupleof[1..$]);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	static void Update(Statement* stmt,  Row* p){
		stmt.compile(_update);
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, p.tupleof[0]);
		}else{
			stmt.bindTuple(p.tupleof[1..$], p.tupleof[0]);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	static void Update(Statement* stmt,  Row* p, RowType id){
		stmt.compile(_update);
		static if( oid >= 0 && ( (Row.table_def.cols[oid].col_type & ColType.ID) !is ColType.ID ) ){
			stmt.bindTuple(p.tupleof, id);
		}else{
			stmt.bindTuple(p.tupleof[1..$], id);
		}
		int code	= stmt.step();
		if( code != SQLITE_DONE ){
			stmt.isErr(code);
		}
		stmt.finalize();
	}
	
	static bool One(Statement* stmt, ref Row* p, RowType id){
		stmt.compile(_select);
		stmt.bindTuple(id);
		bool re	= stmt.fetchTuple(p.tupleof);
		stmt.finalize();
		return re;
	}
	
	static Statement*	stmt_insert;
	static void open_insert(Connection* con){
		close_insert;
		stmt_insert	= Statement.Create(con);
	}
	static void close_insert(){
		if( stmt_insert ){
			stmt_insert.close;
			stmt_insert	= null;
		}
	}
	static void pre_insert(){
		stmt_insert.compile(_insert);
	}
	static void step_insert(Row* p){
		step_insert(stmt_insert, p);
	}
	
	static Statement*	stmt_update;
	static void open_update(Connection* con){
		close_update;
		stmt_update	= Statement.Create(con);
	}
	static void close_update(){
		if( stmt_update ){
			stmt_update.close;
			stmt_update	= null;
		}
	}
	static void pre_update(){
		stmt_update.compile(_update);
	}
	static void step_update(Row* p){
		step_update(stmt_update, p);
	}
	
}


final class TSelect(char[] _sql, T...){
	static const char[] sql		= _sql;
	static const char[] sql_count	= Tsql_count(sql);
	static assert(sql_count != "", sql ~ "\t sql_count error");
	static const int q_all 			= ctfe2_CountQuestionMark(sql);
	static const int q_delta		= q_all - ctfe2_CountQuestionMark(sql_count);
	static assert(T.length > q_all, sql ~ "\tnot match type: " ~ T.stringof);
	alias T[0..$-q_all] 		Tr;
	alias T[$-q_all..$]		Ts;
	alias T[$-q_all..$-q_delta]	Tc;

	//pragma(msg, sql);
	//pragma(msg, sql_count);
	//pragma(msg, "Tr " ~ Tr.stringof);
	//pragma(msg, "Ts " ~ Ts.stringof);
	//pragma(msg, "Tc " ~ Tc.stringof);
	
	Statement*	_stmt;
	Statement*	_stmt_count;
	
	this(Connection* con, char[] _sql = null ){
		open(con);
		_stmt.compile(sql);
		if( _sql is null ){
			_stmt_count.compile(sql_count);
		}else{
			_stmt_count.compile(_sql);
		}
	}
	void open(Connection* con){
		close;
		_stmt		= Statement.Create(con);
		_stmt_count	= Statement.Create(con);
	}
	void compile(){
		_stmt.compile(sql);
		_stmt_count.compile(sql_count);
	}
	void close(){
		if( _stmt ){
			_stmt.close;
			_stmt	= null;
		}
		if( _stmt_count ){
			_stmt_count.close;
			_stmt_count= null;
		}
	}
	
	void reset(){
		_stmt.reset;
		_stmt_count.reset;
	}
	
	int count(Tc tc){
		int count;
		_stmt_count.bindTuple(tc);
		_stmt_count.fetchTuple(count);
		_stmt_count.reset;
		return count;
	}
	
	void select(Ts ts){
		_stmt.bindTuple(ts);
	}
	
	bool fetchOne(ref Tr tr){
		return _stmt.fetchTuple(tr);
	}
}
